Amazon QuickSightで箱ひげ図を表示してみた

はじめに

データアナリティクス事業本部の藤川です。
QuickSightで箱ひげ図を書いてみました。

概要

使用したデータは、国土交通省が提供している地価公示データです。ファイルフォーマットは複数用意されていますが、テキスト(CSV)ファイルを選んでみました。

• 国土数値情報 | 地価公示データ

次のAWSサービスを使用します。
S3に置いたCSVファイルをGlueクローラで取り込み、QuickSightからAthenaのテーブルとして取り込みます。

• Amazon S3
• AWS Glue
• Amazon Athena
• Amazon QuickSight

データを準備

CSVファイルをダウンロード

1. 国土数値情報 | 地価公示データから次のファイルをダウンロードします。

地域	年度	ファイル名
全国	令和2年	L01-2020P-48-01.0a.zip

1. ZIPファイルを解凍し、UTF-8でエンコードしたCSVファイルを作成します。
2. S3に置いたファイルをAthena等でスキャンする際に、コストを下げて、パフォーマンスを上げる目的で、gzip圧縮を掛けます。
3. gzファイルをS3バケットにアップロードします。
4. 次のコマンドを実行するとやってくれます。
   この例では、hello_athenaバケットにlandpriceフォルダを作成し、gzファイルをアップロードしています。

mkdir landprice
cd landprice

<ZIPファイルをダウンロード>

find . -name '*.zip' | xargs -L1 unzip -j -o
rm *.zip *.html *.xml
find . -name '*.csv' | xargs -L1 nkf -S -w --in-place
find . -name '*.csv' | xargs -L1 gzip
aws s3 mb s3://hello_athena
aws s3 sync ../landprice s3://hello_athena/landprice/

1. 地価公示データは全国地方公共団体コードを使用していますが、都道府県名や都道府県コードを含んでいません。 都道府県単位に集計したいので、都道府県コードのCSVファイルを作成します。

都道府県コード,都道府県名
01,北海道
02,青森県
03,岩手県
(中略)
45,宮崎県
46,鹿児島県
47,沖縄県

1. 都道府県コードのCSVファイルを、地価公示データとは別のフォルダにアップロードします。

aws s3 sync ../prefcode s3://hello_athena/prefcode/

Glueクローラで取り込み

地価公示データ

1. AWSマネジメントコンソールからAWS Glueを開きます。
2. クローラを開きます。
3. クローラの追加ボタンをクリックします。
4. 次のように設定します。

   項目	値
   クローラの名前	地価公示データ
   インクルードパス	s3://hello_athena/landprice/
   データベースの追加	hello_athena

1. 完了ボタンをクリックします。
2. クローラの一覧画面に戻るので、今作成した地価公示データにチェックを付けて、クローラの実行ボタンをクリックします。

都道府県コード

1. 先ほどと同様に、クローラの追加ボタンをクリックし、都道府県コード用のクローラを作成します。
2. 次のように設定します。

   項目	値
   クローラの名前	都道府県コード
   インクルードパス	s3://hello_athena/prefcode/
   データベースの追加	hello_athena

テーブル定義を更新

1. AWS Glueのテーブルを開きます。
2. テーブル名をクリックします。
3. 右上にあるスキーマの編集ボタンをクリックします。
4. 所在地コードの右にbigintリンクが表示されているので、リンクをクリックします。
5. 列のタイプをbigintからstringに変更し、更新ボタンをクリックします。
6. 同様に、次の項目をstringに変更します。 | 所在地コード | bigint | string | | 都道府県コード | bigint | string | | 用途 | bigint | string |
7. 右上にある保存ボタンをクリックし、テーブル定義の変更を確定します。

AthenaでカスタムSQLを作成

Glueデータカタログで取り込んだデータをAthenaから参照できるようになりました。
QuickSightにデータソースとして取り込める状態です。
地価公示データと都道府県コードという2つのテーブルを結合して使用したいです。 そこで今回は、QuickSightのカスタムSQLを使って取り込みたいと思います。

1. AWSマネジメントコンソールからAmazon Athenaを開きます。
2. Query editorが開くので、Glueで取り込んだData source、Databaseを開きます。
3. Tablesには、Glueで取り込んだlandpriceとprefcodeというテーブルが表示されていると思います。
4. 右側のQuery editorに次のSQLを入力し、Run queryボタンをクリックしてください。

select
    "都道府県名",
    "駅距離",
    "ｈ２７価格" as "2015年",
    "ｈ２８価格" as "2016年",
    "ｈ２９価格" as "2017年",
    "ｈ３０価格" as "2018年",
    "ｈ３１価格" as "2019年",
    "ｒ２価格" as "2020年"
from
    "hello_athena".landprice a left join "hello_athena".prefcode b
    on substr(a."所在地コード", 1, 2) = b."都道府県コード"
where
    "用途" = '000'

1. 下図のように結果が返ってくれば問題ありません。

QuickSightのカスタムSQLでデータソースを取り込み

データセットを作成

1. QuickSightを開きます。
2. 「データセット」を開きます。
3. 「新しいデータセット」ボタンをクリックします。
4. Athenaボタンをクリックします。
5. データソース名を入力します。
6. データソースを作成ボタンをクリックします。
7. データカタログ名でAwsDataCatalogを選択し、カスタムSQLを使用ボタンをクリックします。
8. 新しいカスタムSQLに地価公示データと名前を入力し、先に作成しておいたカスタムSQLをペーストします。
9. クエリの確認ボタンをクリックします。
10. データセットが作成されました。
11. Visualizeボタンをクリックし、分析を作成します。

箱ひげ図を作成

1. 次のアイコン(箱ひげ図)をクリックします。
   
2. 「都道府県名」、「2020年」を選びます。
   ディメンション(青)とメジャー(緑)は自動的に選択されるため、どちらを先にを選んでも問題ありません。
3. 箱ひげ図が表示されましたが、47都道府県あると小さいので、フィルターしてみます。
4. 「フィルター」、「＋」、「都道府県名」の順にクリックします。
5. 適当な「都道府県名」を選びます。ここで、必ず「適用」ボタンをクリックしてから、「閉じる」ボタンをクリックしてください。
6. 完成した箱ひげ図をPDF形式でダウンロードしました。
   やはり、東京の地価は高いですね...。

さいごに

データの準備がほとんどでしたが、データソースが整えば、意外なほど簡単に箱ひげ図を描画できることがお分かりいただけたでしょうか。
それにしても、いつも思うのですが、日本国政府謹製のオープンデータはShift-JISからUTF-8に変更できないのでしょうか？
UTF-8で提供されるようになれば、変換の手間がないですし、もっと利用促進にも繋がるのではないでしょうか。 （今でもありがたく使わせていただいていますが）